package sgz.ln.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDriver {

	/**
	 * 将一个Excel文件N行保留一行。
	 * 
	 * @param file
	 *            需要处理的Excel文件，处理成功的Excel文件保存在该文件名后加“result”的文件中。
	 * @param numOfUnit
	 *            隔多少行保留一行
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @throws ExcelFormatException
	 * @throws TableFormatException
	 */
	public static void extractExcel(File file, int numOfUnit)
			throws IOException {
		FileInputStream inputStream = new FileInputStream(file);
		Workbook workbook = null;
		try {
			workbook = create(inputStream);
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}
		Sheet sheet = workbook.getSheetAt(0);
		String[] tmpS = file.getAbsolutePath().split("\\.");
		String resultName = "";
		for (int i = 0; i < tmpS.length - 1; i++) {
			resultName += tmpS[i] + ".";
		}
		resultName += "result." + tmpS[tmpS.length - 1];
		File resultFile = new File(resultName);
		Workbook newWorkbook = null;
		if ("xls".equals(tmpS[tmpS.length - 1])) {
			newWorkbook = new HSSFWorkbook();
		} else if ("xlsx".equals(tmpS[tmpS.length - 1])) {
			newWorkbook = new XSSFWorkbook();
		} else
			throw new IOException("文件名后缀不太对！");
		Sheet newSheet = newWorkbook.createSheet();
		int distanceToStore = numOfUnit;
		int nowRowIndex = 0;
		for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			if (distanceToStore != 1) {
				distanceToStore--;
				continue;
			}
			distanceToStore = numOfUnit;
			Row nowRow = newSheet.createRow(nowRowIndex);
			copy(row, nowRow);
			nowRowIndex++;
		}
		inputStream.close();
		FileOutputStream output = new FileOutputStream(resultFile);
		newWorkbook.write(output);
	}

	/**
	 * 对一个row进行copy
	 */
	public static void copy(Row source, Row target) {
		if (source == null || target == null)
			return;
		for (int i = source.getFirstCellNum(); i < source.getLastCellNum(); i++) {
			Cell sourceCell = source.getCell(i);
			Cell targetCell = target.createCell(i);
			targetCell.setCellType(sourceCell.getCellType());
			switch (sourceCell.getCellType()) {
			case Cell.CELL_TYPE_BLANK:
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				targetCell.setCellValue(sourceCell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_FORMULA:
				targetCell.setCellFormula(sourceCell.getCellFormula());
				break;
			case Cell.CELL_TYPE_NUMERIC:
				targetCell.setCellValue(sourceCell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING:
				targetCell.setCellValue(sourceCell.getStringCellValue());
			default:
				break;
			}
		}
	}

	public static Workbook create(InputStream inp) throws IOException,
			InvalidFormatException {
		if (!inp.markSupported()) {
			inp = new PushbackInputStream(inp, 8);
		}
		if (POIFSFileSystem.hasPOIFSHeader(inp)) {
			return new HSSFWorkbook(inp);
		}
		if (POIXMLDocument.hasOOXMLHeader(inp)) {
			return new XSSFWorkbook(OPCPackage.open(inp));
		}
		throw new IllegalArgumentException("你的excel版本目前poi解析不了");
	}
}
